Computerized Investing - Spreadsheet Collection/Spreadsheet Collection.iso/pc/ibm12/retireci.doc

Text File | 1991-07-11 | 14.9 KB | 277 lines

July/August 1990, Computerized Investing
Designing a Retirement Planning Spreadsheet
By Fred Shipley

One of the more important concerns for investors is planning for
the accumulation of wealth to support retirement. In some cases,
investors may have little control over pension investments while
accumulating funds; in others, individuals may directly control the
funds during the accumulation phase.

In this article, we focus on estimating the amount of funds that
will be available after retirement, taking into account self-
directed funds (IRAs and Keoghs), company pension plans, Social
Security payments and the effects of future inflation.

As input, the spreadsheet requires only a small amount of
information. You must determine the current value of your
retirement portfolio or your expected monthly annuity, the amount
of Social Security for which you are eligible, and the current
market value of any other funds you will have for generating
retirement income.

This spreadsheet is designed to take advantage of readily available
information. It is not meant to be a complete financial planning
worksheet. It is designed to provide the basic information needed
to estimate whether you will have an adequate source of funds for
retirement. From that base you can determine how to acquire those
funds.

One of the most important things to remember, and one of the
easiest to ignore, is the effect of compounding on savings value.
The sooner you start saving for your retirement, the more those
funds accumulate and compound

over time. At 6% annual interest, a dollar saved 40 years before
retirement is worth more than three times as much as a dollar saved
20 years before retirement and six times as much as a dollar saved
10 years before retirement. Table 1 shows the impact of compounding
at different rates for different periods of time.

As this table shows, it becomes more and more difficult for an
investor to accumulate funds the longer he waits to start. With
only a short time before retirement, it may be tempting to take
high-risk investments to earn a higher rate of return. The problem
with this strategy is that the high-risk investments may not pay
off. The greater the risk involved, the longer the period of time
it takes to recover from an investment casualty. This is the
principle of "time diversification," allowing a longer period of
time to average out short-term variability in returns.

As an example, the S&P 500 has offered an annually compounded
return of 10% per year since 1926, while T-bills have only offered
a 3.5% annual return. Although the premium in return on stocks has
been substantial, the year-by-year data shows that the minimum
period necessary to guarantee that stocks would earn at least as
much as Treasury bills is 18 years. In other words, even though
common stocks have offered substantial rewards to investors, it can
take a long time to be reasonably sure of actually earning more
from stocks than by simply rolling over your investments in
Treasuries.

Sources of Retirement Income

Most individuals will have retirement income from a number of
sources. The most obvious of these are Social Security; any
company-sponsored pension plan; individual tax-deferred retirement
savings from 401(k) plans, ESOPs, profit-sharing plans, IRAs and
Keoghs; and direct individual savings and investments. Not as
obvious, perhaps, are funds generated from the sale of a principal
residence (if you no longer need the five bedroom home that was
necessary when children were around) or income from paid-up
insurance.

For many individuals, especially those who are relatively young
now, Social Security is not likely to provide a significant portion
of retirement income. People who are retired now can get up to
$11,712 a year in today's dollars in Social Security payments.
These payments are adjusted periodically for changes in the cost of
living. However, the baby boomers are funding these benefits
through their Social Security taxes. When the baby boomers reach
retirement, there will likely be a lot fewer workers paying Social
Security taxes. The number of workers depends on future demographic
trends, but for those retiring early in the next century, current
trends are not encouraging.

The status of future Social Security benefits will be the result of
the political process, and that could be nearly anything. One
possible scenario is that benefits are reduced more and more as
retirees have other sources of retirement income. A conservative
way for younger investors to deal with this potential problem is to
simply factor in nothing for Social Security benefits in the work-
sheet. The only worse scenario is for retirees to not only get no
Social Security benefits if they have other sources of retirement
income, but to also have to pay Social Security taxes on that other
income!

Other than Social Security, most individuals will rely on a
company-sponsored pension plan for the bulk of their retirement
income. These plans vary considerably. Some are vested immediately
(which means that you have an immediate claim on those funds); by
law, all such plans must be fully vested in five years. Vested
pension money is yours, but generally you will only be able to
claim the funds at retirement. In addition, some pension funds are
"portable," which means that they will transfer with you if you
change jobs. Typically, corporate pension plans are not portable;
this feature is generally available only for workers in not-for-
profit enterprises.

Many corporations sponsor other forms of retirement savings plans,
some of which may be tax-deferred. In addition, some companies may
match employees' contributions to these plans. These tax-deferred
plans are usually known as 401 (k) plans, named after the section
of the Internal Revenue Code that authorized them. As a general
guideline, money invested in a tax-deferred plan is worth more than
other savings because you do not have to pay taxes on that income
currently. This rule of thumb, however, must not be treated
cavalierly. If you can earn substantially higher rates of return on
other investments that are not tax-deferred, then those higher
returns can offset the benefits of deferring taxes. This becomes
especially true when you get close to retirement, since the present
value of the tax savings decreases. In other words, if it is
getting very close to the time when you will be drawing retirement
income from tax-deferred sources, then the value of tax deferment
is reduced. Finally, most of this retirement income will be taxed
in some form or other, depending in part on how much other income
you have.

Other Factors in Retirement Planning

Once you have anticipated your financial needs and made some plans
to provide for those needs, you can begin asking what could happen
to ruin your plans. This is an area in which spreadsheets shine,
since the problem invites examining different scenarios. There are
many factors you could explore, but there are three variables we
will focus on here--the number of years you will need to rely on
the accumulated funds after retirement, the rate of return you can
earn both pre- and post-retirement, and the rate of change in
purchasing power over time. To deal with each of these issues, we
will set up different data tables.

One of the more important issues is the erosion of investment value
through inflation. While many investments are touted as "inflation
hedges," that is a much abused term. Usually, an upsurge in the
rate of inflation will initially cause the values of real assets to
increase, while the values of financial assets decrease as rates of
return go up to compensate for the increased inflation. Eventually,
however, the increased returns on financial assets should translate
into higher values. For equities, as companies increase prices,
revenue growth and cost containment measures will eventually
(hopefully) lead to increased profitability and dividends.
Ultimately this should lead to increased stock values. For debt
securities, increases in market returns will cause newly issued
debt to be offered at higher initial interest rates. Older debt,
issued at lower coupon rates of interest, will not recover in price
until inflation decreases and interest rates drop. If rates do
eventually drop, however, debt at the higher interest rates will
increase substantially in price, especially long-term debt.

The impact of inflation on your retirement planning is not limited
to its impact on your spending power. Inflation has a real impact
on your asset allocation and on your ability to generate the
necessary funds for retirement. Many financial planners would be
comfortable with the assumption that the real return (the actual
return minus the decrease in purchasing power) offered by different
classes of investments remains constant. With your spreadsheet,
however, you can examine other possibilities. This is essential,
since it may be many years before financial markets "return to
normal."

The period of time from the late 1960s (starting about 1968)
through 1982 is illustrative in this regard. This period was marked
by high inflation, external economic shocks like the oil embargo
and the consequent increase in oil and other energy costs, and by
internal economic shocks caused by trying to fight a major war as
well as substantially increase spending for social programs. While
all this was occurring, the Fed was trying to maintain economic
growth, and the result was high inflation, stagnating stock prices
and very high interest rates. It was not until 1982 that interest
rates started to drop significantly and stock prices recovered.
Thus, we experienced a 14 year period in which "normal" was not
normal, so planning based on normal considerations would not work.

The spreadsheet will not allow you to predict when such periods are
about to occur; it will, however, allow you to examine the impact
of inflation on your retirement portfolio.

The Effect of Time on Retirement Income

While most investors are aware that longer lives mean a greater
need for retirement planning, there are many factors that will
affect this need. With most pension plans, there are a number of
options you can choose for your retirement payout. Some involve a
commitment to provide income to yourself, others have a commitment
to provide income to your spouse, still others may have a minimum
numbers of years of payout guaranteed. While individual family
circumstances will have a significant impact on your choices, you
should examine what you are giving up for the extra security
involved with a guarantee or alternative payout methods.

There are at least two factors involved here. First, any kind of
guarantee or minimum payout increases the actuarial risk of the
company, and therefore lowers the dollar payout to you. In
addition, though, you must weigh the potential return you could
earn if you chose the higher payout option. If invested wisely,
this could more than compensate for the guarantee. It is necessary,
however, that the excess payments be invested. Second, in all of
these scenarios you must consider the likelihood of living longer
(or shorter) than the actuarial average.

You must be especially careful when dealing with the income
generated by your own investment portfolio. Remember that the way
present value formulations work, once you specify a period of time
for the income stream, it is exhausted when that period expires.
That is, the income stream depletes not only interest income, but
principal as well. If you want to live solely on the income
generated by your portfolio, your cash flow will be less. The
income provided by pension plans is structured differently, so the
income stream remains constant. The difficulty with fixed pensions,
though, is that inflation can destroy their purchasing power
quickly. The only way of making up this difference is through your
own investment portfolio. (OK, Social Security payments are cur-
rently still being adjusted for inflation. But a wise investor will
realize that Social Security is a relatively small part of the
retirement income.)

Setting Up the Spreadsheet

You must enter the current date (cell C2), your date of birth (E5),
anticipated retirement age (E6), current income level (E9), and an
anticipated annual percentage raise (E10). Headings for cells with
calculated data are shown in regular type. The spreadsheet
determines your current age and years until retirement, estimates
the income anticipated when you reach retirement, and from that
estimates your necessary retirement income. The estimated
retirement income is assumed to be 70% of your pre-retirement
income, a standard rule of thumb, but you can modify that
percentage (cell E14).

Once this information is entered, you must enter the anticipated
level of benefits from Social Security (in today's dollars) in cell
E17, the inflation rate at which Social Security benefits will
increase (E18), and your company pension benefits (E37) . The
anticipated Social Security benefits shown in cell E36 are
determined by compounding the current benefits (E17) at the long-
term rate of inflation (E18). As an alternative, you could just use
the current level of benefits (by entering a zero rate of inflation
in cell E18), or even enter a zero amount into cell E17. Either
alternative would give a more conservative estimate of future
retirement income. Finally, you must enter the current value of
your personal retirement savings portfolio (E24), and the number of
years you will need retirement income (E20). The spreadsheet then
determines if there is any discrepancy between your estimated
retirement needs and your available income sources. Any discrepancy
is indicated.

At this point you must indicate what you expect to be able to earn
on your investment portfolio (E25). The spreadsheet then determines
what (if any) additional capital you need to add to your savings
each month until retirement. This is based upon the values you
entered in cell E32 for your post-retirement rate of return (we
used 6% as a reasonable low-risk return) and the number of years
you expect to need this income (E20).

This, of course, is not a complete solution to all your retirement
planning needs. Rather, we have tried to design a spreadsheet that
will allow you to get a handle on what it will take to generate the
level of retirement income you desire. From that, you can see
whether this level is feasible, and begin planning to achieve it.
One fact the spreadsheet illustrates is the large amount of capital
required for retirement. To the extent that you must provide this
capital yourself, discipline and a coherent plan are critical. Most
investors do not have to be reminded that there are many demands on
their funds over a lifetime. Putting a low priority on retirement
just because it is (relatively) far off can be disastrous.

(C) Copyright 1991 by the
American Association of Individual Investors